package com.artfii.fluentsql.core.jdbc;

import com.artfii.fluentsql.core.LambdaQuery;
import com.artfii.fluentsql.core.Page;
import com.artfii.fluentsql.core.TableFieldSpell;
import com.artfii.fluentsql.core.exception.FluentSqlException;
import com.artfii.fluentsql.core.mock.Dept;
import com.artfii.fluentsql.core.mock.User;
import com.artfii.fluentsql.core.tools.*;

import java.util.*;
import java.util.logging.Logger;

import static com.artfii.fluentsql.core.BaseQuery.Opt.INSERT;

/**
 * Function: 查询表达式
 *
 * @Autor: leeton
 * @Date : 2/27/18
 */
public class Qe<T> extends LambdaQuery<T> {
    protected static final Logger logger = Logger.getLogger("Qe");

    public Qe(){
        this.test_mode = true; //没有传入实体类，就当做是测试模式
        this.show_debug_sql = true;
    }
    public Qe(Class<T> clz) {
        super(clz);
    }

    public Qe(Class<T> clz,FluentConnection fluentConnection) {
        super(clz,fluentConnection);
    }

    @Override
    public void engine() {
        this.engine = ConnectionBuilder.SqlEngine.fluentsql;
    }

    public Qe<T> test(){
        this.test_mode = true;
        this.debug();
        return this;
    }

    @Override
    public Qe<T> debug() {
        this.show_debug_sql = true;
        return this;
    }

    @Override
    public Qe<T> condition() {
        Qe q = new Qe(this.clz);
        q.clear();
        q.onlyConition = true;
        return q;
    }

    // ====== 集成查询方法 BEGIN ====================================================================================================
    public T to() {
        return to(this.clz);
    }

    public T to(Class tClass) {
        T obj =  getObj(buildSymbolsql(), this.where_params,tClass);
        clear();
        return obj;
    }

    public Long toCount() {
        String symbolSql = buildCountSymbolSql();
        printSql(symbolSql);
        Long r = getObj(symbolSql, this.where_params, Long.class);

        return r;
    }

    public List<T> toList() {
        return toList(this.clz);
    }

    public List toList(Class tClass) {
        return getList(buildSymbolsql(), tClass);
    }

    public int toDel() {
        delete();
        String sql = buildSymbolsql();
        printSql(sql);
        int rows = fluentSqlQuery.execute(sql, this.where_params);
        clear();
        return rows;
    }

    public int toUpdate(Object pojo) {
        if (pojo instanceof Collection) {
            throw new FluentSqlException("Only update of ONE POJO.");
        }
        update(pojo,true);
        String sql = buildSymbolsql();
        printSql(sql);
        int rows = fluentSqlQuery.execute(sql, this.body_params);
        return rows;
    }

    public int toBatchUpdate(List<?> batchValues) {
        if (null == batchValues || batchValues.size() == 0) return 0;
        if(batchValues.size() >1000) throw new FluentSqlException("Batch update size must be less than 1000.");
        List<Map<Integer, Object>> paramList = buildBatchUpdateSqlAndParams(batchValues);
        printSql(this.symbolsql);
        int rows = fluentSqlQuery.batchSave(this.symbolsql, paramList);
        return rows;
    }
    public int toBatchInsert(List<?> batchValues) {
        if (null == batchValues || batchValues.size() == 0) return 0;
        if(batchValues.size() > 1000) throw new FluentSqlException("Batch update size must be less than 1000.");
        List<Map<Integer, Object>> paramList = buildBatchInsertSqlAndParams(batchValues);
        String batchSql = buildSymbolsql();
        printSql(batchSql);
        int rows = fluentSqlQuery.batchSave(batchSql, paramList);
        return rows;
    }

    private List<Map<Integer, Object>> buildBatchInsertSqlAndParams(List<?> batchValues) {
        StringBuffer batchSql = new StringBuffer(1000);
        batchSql = templateMap.get("insert");
        batchSql.append(INSERT.symbol).append("`").append(getTableName(this.clz)).append("`");
        batchSql.append(builInsertSqlBody(paramsNameIdx));
        List<Map<Integer, Object>> paramList = setParamVolWithNameIdx(batchValues,paramsNameIdx);
        paramsNameIdx.clear();
        return paramList;
    }

    private List<Map<Integer, Object>> buildBatchUpdateSqlAndParams(List<?> batchValues) {
        List<Map<Integer, Object>> paramList = new ArrayList<>(batchValues.size());
        for (Object o : batchValues) {
            update(o,false);
            if(StringKit.isBlank(this.symbolsql)){
                this.symbolsql = buildSymbolsql();
            }
            Map<Integer,Object> vmap = toSigleSqlAndParams(this.symbolsql);
            paramList.add(vmap);
        }
        return paramList;
    }

    private StringBuffer builInsertSqlBody(Map<String, Integer> paramsNameIdx) {
        StringBuffer sqlBody = new StringBuffer(128);
        Map<String, Object> fieldMap = new HashMap<String, Object>(32);
        BeanMapUtils.builder().setSkipNullVal(false).setSkipTransient(true).toUnderline()
                .c(ObjFactory.instanceOf(this.clz), fieldMap);
        sqlBody.append(" (");
        int i = 1;
        for (String k : fieldMap.keySet()) {//key列表
            String key = TableFieldSpell.UNDERLINE.trans(k);
            sqlBody.append("`").append(key).append("`").append(",");
            paramsNameIdx.put(k, i++);
        }
        sqlBody.deleteCharAt(sqlBody.length() - 1);
        sqlBody.append(") VALUES (");
        for (String k : fieldMap.keySet()) {// VALUES的占位符
            sqlBody.append("?,");
        }
        sqlBody.deleteCharAt(sqlBody.length() - 1);
        sqlBody.append(")");
        return sqlBody;
    }

    private StringBuffer builUpdateSqlBody(Map<String, Integer> paramsNameIdx) {
        StringBuffer sql = new StringBuffer(128);
        Map<String, Object> fieldMap = new HashMap<String, Object>(20);
        Object entity = ObjFactory.instanceOf(this.clz);
        BeanMapUtils.builder().setSkipNullVal(false).setSkipTransient(true).toUnderline().c(entity, fieldMap);
        Attr id = Attr.getRealIdAttr(entity);
        int i = 1;
        sql.append(" SET ");
        for (String k : fieldMap.keySet()) {// build SET语句
            if (k.equalsIgnoreCase(id.getName())) continue; //跳过id
            sql.append(k).append("=").append("?").append(",");
            paramsNameIdx.put(k, i++);
        }
        sql.deleteCharAt(sql.length() - 1);
        fieldMap.clear();
        return sql;
    }

    private List<Map<Integer, Object>> setParamVolWithNameIdx(List<?> batchValues, Map<String,Integer> paramsNameIdx) {
        List<Map<Integer, Object>> paramList = new ArrayList<>(batchValues.size());
        for (Object o : batchValues) {
            Map<String, Object> tmap = new HashMap();
            BeanMapUtils.builder().setSkipNullVal(false).setSkipTransient(true).toUnderline().c(o, tmap);
            Map<Integer, Object> valMap = new HashMap<>();//k/v
            for (String key : paramsNameIdx.keySet()){
                valMap.put(paramsNameIdx.get(key), tmap.get(key));
            }
            paramList.add(valMap);
            tmap = null;
        }
        return paramList;
    }

    private Map<Integer, Object> toSigleSqlAndParams(String sql){
        int i = 1;
        Map<Integer,Object> vMap = new HashMap(where_params.size());
        for (String k : body_params.keySet()) {
            vMap.put(i++,body_params.get(k));
            sql = sql.replace(k,"?");
        }
        for (String k : where_params.keySet()) {
            vMap.put(i++,where_params.get(k));
            sql = sql.replace(k,"?");
        }
        this.symbolsql = sql;
        return vMap;
    }



    public int toSave(Object pojo) {
        if (pojo instanceof Collection) {
            throw new FluentSqlException("Only save of ONE POJO.");
        }
        save(pojo);
        String sql = buildSymbolsql();
        printSql(sql);
        int rows = fluentSqlQuery.execute(sql,this.body_params);
        clear();
        return rows;
    }

    public Page<T> toPage(Page page) {
        return toPage(this.clz, page);
    }

    public Page<T> toPage(Class clz, Page page) {
        page = getPage(clz, page);
        clear();
        return page;
    }

    /**
     * 返回 PAGE 查询结果集
     * 注意不要传入 limit 语句
     *
     * @param clazz
     * @param page
     * @return
     */
    private Page<T> getPage(Class<T> clazz, Page page) {
        StringBuffer limit = templateMap.get("limit");
        if (limit.length() > 0) {
            throw new FluentSqlException("分页参数不要通过[limit]传入.");
        }
        Long count = page.getTotal() == 0 ? toCount() : page.getTotal();
        List<T> list = Collections.emptyList();
        if (count > 0) {
            long pageNumber = page.getPageNumber();
            long pageSize = page.getPageSize();
            long offset = (pageNumber - 1) * pageSize;
            settingLimit(offset, pageSize);
            list = getList(buildSymbolsql(), clazz);
        }
        page.setTotal(count);
        page.setItems(list);
        return page;
    }

    /**
     * 返回列表
     *
     * @param sql SQL
     * @param tClass
     * @return
     */
    public List getList(String sql, Class<T> tClass) {
        return getList(sql,this.where_params, tClass);
    }

    public List getList(String sql,Map<String,Object>paramMap, Class<T> tClass) {
        printSql(sql);
        List<T> list = fluentSqlQuery.getList(sql, paramMap, tClass);
        list = 0 == list.size() ? new ArrayList<>() : list;
        return list;
    }

    public <T> T getObj(String sql, Map<String, Object> params, Class tClass) {
        printSql(sql);
        T o = (T) fluentSqlQuery.getObject(sql, params, tClass);
        return o;
    }

    // ============================================ PRIVATE METHODS  ============================================


    // ====== 集成查询方法 END ====================================================================================================


    public static void main(String[] args) throws Exception {
        FluentConnection connection = FluentConnection.buildMockMe();
        String sql = new Qe(User.class)
                .select("user_name")
                .select("age")
            .andIn("dept_id", new Qe(Dept.class).select("id").andGt("id", 0))//子查询
//                .select(Qf.max(User::getAge,"maxAge"))
//                        .sum("id",Dept.class)
//                .sumCase("id", 1, "money", "money")
                .leftJoin(Dept.class)
//                .andLike("user_uame", "alice")
//                .andIn("id", new Integer[]{1, 2, 3})
//                .andBetween("create_date", new Date(), new Date())
                .or(new Qe(User.class).andIn("id", new Integer[]{1, 2, 3}), new Qe(User.class)
                        .andEq("user_name", "alice"))

//                .andEq("user_name","select * from user").checkHack() //测试 SQL 注入
                .group("dept_id")
                .having("dept_id", Opt.GT, 0)
                .asc("dept_id")
                .desc("user_name")
                .build();

        PrintUtil.yellowln(sql);

/*
        String between = new Qe().andBetween(SysUser.Dao.createDate, new Date(), new Date()).build();
        System.out.println("between=" + between);
*/

/*        String del = new Qe(SysUser.class).del().andEq("dept_id", 1).build();
        System.out.println("del= " + del);

        String count = new Qe(SysUser.class).andEq("dept_id", 1).count();
        System.out.println("count= " + count);*/

        User u = new User();
        u.setId(100);
        u.setUserName("alice");

        User u2 = new User();
        u2.setId(200);
        u2.setUserName("alice200");
        List<User> userList = new ArrayList<>();
        userList.add(u);
        userList.add(u2);

//        new Qe(User.class, FluentConnection.buildMockMe()).toUpdate(u);
        String updateSql =  new Qe<User>(User.class).andEq(User::getAge,19).update(u).build();
        System.err.println(updateSql);
//        int rows = new Qe(User.class, FluentConnection.buildMockMe()).toBatchInsert(userList);
//        System.err.println("update rows:" + rows);


    }


}
